﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace DotNetDal.Core.Model
{
    public class Database
    {
        public string Name { get; set; }
        public List<Schema> Schemas { get; set; }

        public static Database RetrieveDatabase()
        {
            string sql = @"--Schemas
                        select CATALOG_NAME, SCHEMA_NAME
                        from INFORMATION_SCHEMA.SCHEMATA
                        where Schema_Owner='dbo'
                        order by SCHEMA_NAME

                        --Tables
                        select table_schema, table_name, table_type
                        from INFORMATION_SCHEMA.TABLES
                        where table_type in ('BASE TABLE','VIEW')
                        ORDER BY table_name

                        --Columns
                        select isc.TABLE_SCHEMA, isc.TABLE_NAME, isc.COLUMN_NAME, isc.COLUMN_DEFAULT, isc.IS_NULLABLE, isc.CHARACTER_MAXIMUM_LENGTH, isc.DATA_TYPE, isc.NUMERIC_PRECISION, isc.NUMERIC_SCALE, c.is_identity AS IsIdentity, ORDINAL_POSITION, def.Definition AS DefaultDefinition, def.name AS DefaultConstraintName
		                        ,case when pk.COLUMN_NAME is null then 0 else 1 end as IsPrimaryKey
                        from sys.columns c 
                        inner join sys.tables t on c.object_id = t.object_id 
                        inner join INFORMATION_SCHEMA.COLUMNS isc on schema_id(isc.TABLE_SCHEMA) = t.schema_id and isc.TABLE_NAME = t.name and isc.COLUMN_NAME = c.name 
                        left outer join sys.default_constraints def ON def.parent_object_id = c.object_id AND def.parent_column_id = c.column_id
                        left outer join (select c.TABLE_NAME, c.COLUMN_NAME
				                        from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk 
				                        inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c on c.table_name = pk.table_name and c.constraint_name = pk.constraint_name
				                        where constraint_type = 'PRIMARY KEY') as
                        pk on isc.TABLE_NAME=pk.TABLE_NAME and isc.COLUMN_NAME=pk.COLUMN_NAME
                        union
                        select isc.TABLE_SCHEMA, isc.TABLE_NAME, isc.COLUMN_NAME, isc.COLUMN_DEFAULT, isc.IS_NULLABLE, isc.CHARACTER_MAXIMUM_LENGTH, isc.DATA_TYPE, isc.NUMERIC_PRECISION, isc.NUMERIC_SCALE, c.is_identity AS IsIdentity, ORDINAL_POSITION, def.Definition AS DefaultDefinition, def.name AS DefaultConstraintName
		                        ,case when pk.COLUMN_NAME is null then 0 else 1 end as IsPrimaryKey
                        from sys.columns c 
                        inner join sys.views v on c.object_id = v.object_id 
                        inner join INFORMATION_SCHEMA.COLUMNS isc on schema_id(isc.TABLE_SCHEMA) = v.schema_id and isc.TABLE_NAME = v.name and isc.COLUMN_NAME = c.name 
                        left outer join sys.default_constraints def ON def.parent_object_id = c.object_id AND def.parent_column_id = c.column_id
                        left outer join (select c.TABLE_NAME, c.COLUMN_NAME
				                        from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk 
				                        inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c on c.table_name = pk.table_name and c.constraint_name = pk.constraint_name
				                        where constraint_type = 'PRIMARY KEY') as
                        pk on isc.TABLE_NAME=pk.TABLE_NAME and isc.COLUMN_NAME=pk.COLUMN_NAME
                        ORDER BY ORDINAL_POSITION, isc.COLUMN_NAME";

            DataSet ds = SqlServer.GetDataSet(sql);

            DataTable schemaTable = ds.Tables[0];
            DataTable tableTable = ds.Tables[1];
            DataTable fieldTable = ds.Tables[2];

            Database db = new Database();
            db.Schemas = new List<Schema>();

            foreach (DataRow schemaRow in schemaTable.Rows)
            {
                db.Name = schemaRow["CATALOG_NAME"].ToString();

                Schema s = new Schema();
                s.Database = db;
                s.Name = schemaRow["SCHEMA_NAME"].ToString();
                s.Tables = new List<Table>();

                db.Schemas.Add(s);

                foreach (DataRow tableTableRow in tableTable.Select("TABLE_SCHEMA = '" + s.Name + "'"))
                {
                    Table t = new Table();
                    t.Schema = s;
                    t.Name = tableTableRow["TABLE_NAME"].ToString();
                    t.Type = tableTableRow["TABLE_TYPE"].ToString() == "BASE TABLE" ? DotNetDal.Core.Model.Table.TableType.BaseTable : Table.TableType.View;
                    t.Fields = new List<Field>();

                    s.Tables.Add(t);

                    foreach (DataRow fieldTableRow in fieldTable.Select("TABLE_SCHEMA = '" + s.Name + "' AND TABLE_NAME = '" + t.Name + "'"))
                    {
                        Field f = new Field();
                        f.Table = t;
                        f.Name = fieldTableRow["COLUMN_NAME"].ToString();
                        f.Default = fieldTableRow["COLUMN_DEFAULT"].ToString();
                        f.IsNullable = fieldTableRow["IS_NULLABLE"].ToString() == "NO" ? false : true;
                        f.SqlDataType = fieldTableRow["DATA_TYPE"].ToString();
                        f.IsIdentity = fieldTableRow["IsIdentity"].ToString() == "False" ? false : true;
                        f.IsPrimaryKey = fieldTableRow["IsPrimaryKey"].ToString() == "0" ? false : true;

                        if (f.SqlDataType != "xml")
                            t.Fields.Add(f);
                    }
                }
            }

            return db;
        }
    }
}
